package com.yenlien.traceyun.business.dao;

import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.business.entity.ProcessDetail;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface ProcessDetailDao extends BaseJpaDao<ProcessDetail> {


    @Query(value = "SELECT COUNT(total_number) FROM t_plant_process_detail WHERE company_id = ?1", nativeQuery = true)
    String prodNumCount(String companyId);


    @Query(value = "SELECT IFNULL(SUM(total_number), 0) totalNum FROM t_plant_process_detail WHERE DATE_FORMAT(`createtime`, '%Y-%m') = ?1 AND company_id = ?2", nativeQuery = true)
    Float getTotalNum(String time, String companyId);

    @Query(value = "SELECT IFNULL(SUM(total_number), 0) totalNum FROM t_plant_process_detail WHERE DATE_FORMAT(`createtime`, '%Y-%m') = ?1 AND company_id = ?2 AND product_name = ?3", nativeQuery = true)
    Float getTotalNum(String time, String companyId, String productName);

    @Query(value = "SELECT product_name jyName, total, num, CONCAT( round( num / total * 100.00, 3), '%') percent FROM " +
            "( SELECT * FROM ( SELECT product_name, SUM(total_number) num FROM t_plant_process_detail WHERE company_id = ?1 AND createtime BETWEEN ?2 AND ?3 GROUP BY product_name) t1 " +
            " JOIN (SELECT SUM(total_number) total FROM t_plant_process_detail WHERE company_id = ?1 AND createtime BETWEEN ?2 AND ?3) t2) t", nativeQuery = true)
    List<JSONObject> getPercent(String companyId, String startTime, String endTime);



    ProcessDetail findById(String id);

    List<ProcessDetail> findByProcessId(String processId);

    @Query(value = "select * from t_plant_process_detail where process_id = ?1 order by createtime desc LIMIT 1", nativeQuery = true)
    ProcessDetail findProcessId(String processId);

    @Query(value = "select product_name from t_plant_process_detail where company_id = ?1 group by product_name", nativeQuery = true)
    List<String> findSelect(String companyId);

    ProcessDetail findByBatch(String batch);


    Page<ProcessDetail> findByTypeAndCompanyId(String type, String companyId, Pageable pageable);

    Page<ProcessDetail> findByCompanyId(String companyId, Pageable pageable);



    @Query(value = "SELECT tc.credit_code, tppd.batch batch_code, tppd.total_number amount FROM t_plant_process_detail tppd LEFT JOIN t_company tc ON tppd.company_id = tc.id " +
            "WHERE tc.parent_id IN (?1) AND tc.dock_province = '1'", nativeQuery = true)
    List<JSONObject> dock3(List<String> parentIdList);

    // t_plant_process_material 的batch 就是 reap采收 的 batch
    @Query(value = "SELECT tppd.id id, tppd.batch batch_code, tppd.total_number amount FROM t_plant_process_detail tppd " +
            "JOIN t_plant_process_material tppm ON tppd.process_id = tppm.process_id WHERE tppd.company_id = ?1 AND tppm.batch = ?2", nativeQuery = true)
    List<JSONObject> dock3(String companyId, String reapBatch);
}
